IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vrptDiplomaHolders]'))
DROP VIEW [vrptDiplomaHolders]
go
CREATE VIEW [vrptDiplomaHolders]
AS
-- used by report rptDiplomaHolders
SELECT   de.DiplomaTypeId, de.StudentId, de.DiplomaEarnedId, d.DiplomaType, 
	YEAR(pp.PeriodProcessDate) AS EarnedYear, s.FirstName, s.LastName, s.DateOfBirth, 
         rtrim(ltrim(isnull(s.Company,''))) as Company,  -- do not sent nulls to report, and also trim records
         s.IdNumber, s.LastName + ' ' + s.FirstName AS StudentFullName, 
         s.HomePhone, s.OfficePhone, s.MobilePhone, s.Fax, s.Email, s.InformCompanyFlag, 
         s.isMember, dbo.lpadnum(s.MemberNumber, 6) as MemberNumber, 
         dst.DiplomaSuperTypeCode, dst.DiplomaSuperTypeName, 
         d.DiplomaSuperTypeId, p.PeriodDate, p.PeriodCode, p.PeriodName, 
         p.PeriodId, pp.PeriodProcessDate AS EarnedDate, de.SerialNo
FROM         dbo.DiplomaEarned AS de INNER JOIN
                      dbo.DiplomaType AS d ON de.DiplomaTypeId = d.DiplomaTypeId INNER JOIN
                      dbo.Student AS s ON de.StudentId = s.StudentId INNER JOIN
                      dbo.DiplomaSuperType AS dst ON d.DiplomaSuperTypeId = dst.DiplomaSuperTypeId INNER JOIN
                      dbo.PeriodProcess AS pp ON de.EarnedPeriodProcessId = pp.PeriodProcessId INNER JOIN
                      dbo.Period AS p ON pp.PeriodId = p.PeriodId

go
